<?php
namespace common\service;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelService extends BaseService
{
    /**
     * 导出表格
     * @param array $data 数据.
     * @see
     * $data = [
     *    ['id','名称','年龄'],
     *    ['id'=>1,'name'=>'a','age'=>12],
     *    ['id'=>2,'name'=>'b','age'=>12],
     * ];
     * @param string $full_path 加文件名的全路径.
     * @param bool $flag 是否存文件 默认不存.
     * @return bool
     */
    public static function exportExcel($data,$full_path,$flag = false){
        $pos = strrpos($full_path,'.');
        if(!$pos){
            return false;
        }
        $ext = substr($full_path,$pos);
        $arr = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        $arr = array_slice($arr,0,count($data[0]));
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        foreach ($data as $key=>$row){
            $row = array_values($row);
            foreach ($arr as $k=>$item)
            {
                $sheet->setCellValue($item.($key+1), ' '.$row[$k]);
            }
        }
        switch ($ext){
            case '.xls':
                $writer = new Xls($spreadsheet);
                break;
            case '.xlsx':
                $writer = new Xlsx($spreadsheet);
                break;
            case '.csv':
                $writer = new Csv($spreadsheet);
                break;
        }

        if($flag){
            $writer->save($full_path);
            return true;
        }
        switch ($ext){
            case '.xls':
                header('Content-Type: application/vnd.ms-excel');
                break;
            case '.xlsx':
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                break;
            case '.csv':
                header('Content-Type: text/csv');
                break;
        }


        header('Content-Disposition: attachment;filename="'.$full_path.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet,ucfirst(trim($ext,'.')));
        $writer->save('php://output');
        exit;
    }


    /**
     * 读取文件内容.
     * @param $full_path
     * @return array|bool
     */
    public static function importExcel($full_path){
        $pos = strrpos($full_path,'.');
        if(!$pos){
            return false;
        }
        $ext = substr($full_path,$pos);
        try{
            $reader = IOFactory::createReader(ucfirst(trim($ext,'.')));
            $spreadsheet = $reader->load($full_path);// 获取当前的活动表格.
            // 这个要注意下.
            $data = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        }catch (\Exception $e){
            var_dump($e->getMessage());exit;
            // 这里是防止出错.
            return false;
        }

        return $data;
    }

}